<!DOCTYPE html>












  


<html class="theme-next muse use-motion" lang="zh-CN">
<head>
  <meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2"/>
<meta name="theme-color" content="#fff">



  
  
  <link rel="stylesheet" href="/lib/needsharebutton/needsharebutton.css">




  
  
    
      
    
    
      
    
  <script src="//cdn.bootcss.com/pace/1.0.2/pace.min.js"></script>
  <link href="//cdn.bootcss.com/pace/1.0.2/themes/blue/pace-theme-flash.min.css" rel="stylesheet">







<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />





  <script>
  (function(i,s,o,g,r,a,m){i["DaoVoiceObject"]=r;i[r]=i[r]||function(){(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;a.charset="utf-8";m.parentNode.insertBefore(a,m)})(window,document,"script",('https:' == document.location.protocol ? 'https:' : 'http:') + "//widget.daovoice.io/widget/0f81ff2f.js","daovoice")
  daovoice('init', {
      app_id: "b3c7d22e"
    });
  daovoice('update');
  </script>








  <meta name="baidu-site-verification" content="true" />







  
  
    
  
  <link href="//cdn.jsdelivr.net/fancybox/2.1.5/jquery.fancybox.min.css" rel="stylesheet" type="text/css" />




  
  
  
  

  
    
    
  

  
    
      
    

    
  

  

  

  
    
      
    

    
  

  
    
    
    <link href="https://fonts.cat.net/css?family=Lato:300,300italic,400,400italic,700,700italic|Roboto Slab:300,300italic,400,400italic,700,700italic|Roboto Slab:300,300italic,400,400italic,700,700italic&subset=latin,latin-ext" rel="stylesheet" type="text/css">
  






  

<link href="//maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" type="text/css" />

<link href="/css/main.css?v=6.4.1" rel="stylesheet" type="text/css" />


  <link rel="apple-touch-icon" sizes="180x180" href="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/favicon1.ico?v=6.4.1">


  <link rel="icon" type="image/png" sizes="32x32" href="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/favicon1.ico?v=6.4.1">


  <link rel="icon" type="image/png" sizes="16x16" href="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/favicon1.ico?v=6.4.1">


  <link rel="mask-icon" href="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/favicon1.ico?v=6.4.1" color="#fff">









<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/',
    scheme: 'Muse',
    version: '6.4.1',
    sidebar: {"position":"left","display":"post","offset":12,"b2t":false,"scrollpercent":true,"onmobile":true},
    fancybox: true,
    fastclick: false,
    lazyload: false,
    tabs: true,
    motion: {"enable":true,"async":true,"transition":{"post_block":"perspectiveLeftIn","post_header":"fadeIn","post_body":"fadeIn","coll_header":"perspectiveLeftIn","sidebar":"slideUpIn"}},
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"输入关键字","hits_empty":"没有找到与 ${query} 相关的内容","hits_stats":"${hits}条相关记录，共耗时${time} ms"}
    }
  };
</script>


  



<script>
    (function(){
        if(''){
            if (prompt('请输入文章密码') !== ''){
                alert('密码错误！');
                if (history.length === 1) {
                    location.replace("https://yfzhou.coding.me/"); // 这里替换成你的首页
                } else {
                    history.back();
                }
            }
        }
    })();
</script>

  <meta name="description" content="高并发大流量的互联网架构，一般通过服务层来访问数据库，随着数据量的增大，数据库需要进行水平切分，分库后将数据分布到不同的数据库实例（甚至物理机器）上，以达到降低数据量，增加实例数的扩容目的。">
<meta name="keywords" content="数据库">
<meta property="og:type" content="article">
<meta property="og:title" content="业界难题 - 『跨库分页』的四种方案">
<meta property="og:url" content="https://yfzhou.coding.me/2018/09/10/业界难题 - 『跨库分页』的四种方案/index.html">
<meta property="og:site_name" content="Felix">
<meta property="og:description" content="高并发大流量的互联网架构，一般通过服务层来访问数据库，随着数据量的增大，数据库需要进行水平切分，分库后将数据分布到不同的数据库实例（甚至物理机器）上，以达到降低数据量，增加实例数的扩容目的。">
<meta property="og:locale" content="zh-CN">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/1.webp">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/2.webp">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/3.webp">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/4.webp">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/5.webp">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/6.webp">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/7.webp">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/8.webp">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/9.webp">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/10.webp">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/11.webp">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/12.webp">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/13.webp">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/14.webp">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/15.webp">
<meta property="og:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/16.webp">
<meta property="og:updated_time" content="2019-04-17T08:59:25.140Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="业界难题 - 『跨库分页』的四种方案">
<meta name="twitter:description" content="高并发大流量的互联网架构，一般通过服务层来访问数据库，随着数据量的增大，数据库需要进行水平切分，分库后将数据分布到不同的数据库实例（甚至物理机器）上，以达到降低数据量，增加实例数的扩容目的。">
<meta name="twitter:image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/1.webp">



  <link rel="alternate" href="/atom.xml" title="Felix" type="application/atom+xml" />




  <link rel="canonical" href="https://yfzhou.coding.me/2018/09/10/业界难题 - 『跨库分页』的四种方案/"/>



<script type="text/javascript" id="page.configurations">
  CONFIG.page = {
    sidebar: "",
  };
</script>

  <title>业界难题 - 『跨库分页』的四种方案 | Felix</title>
  









  <noscript>
  <style type="text/css">
    .use-motion .motion-element,
    .use-motion .brand,
    .use-motion .menu-item,
    .sidebar-inner,
    .use-motion .post-block,
    .use-motion .pagination,
    .use-motion .comments,
    .use-motion .post-header,
    .use-motion .post-body,
    .use-motion .collection-title { opacity: initial; }

    .use-motion .logo,
    .use-motion .site-title,
    .use-motion .site-subtitle {
      opacity: initial;
      top: initial;
    }

    .use-motion {
      .logo-line-before i { left: initial; }
      .logo-line-after i { right: initial; }
    }
  </style>
</noscript>

</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-CN">

  
  
    
  

  <div class="container sidebar-position-left page-post-detail">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta ">
    

    <div class="custom-logo-site-title">
      <a href="/" class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">Felix</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
    
      
        <p class="site-subtitle">Viva La Vida</p>
      
    
  </div>

  <div class="site-nav-toggle">
    <button aria-label="切换导航栏">
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>



<nav class="site-nav">
  
    <ul id="menu" class="menu">
      
        
        
        
          
          <li class="menu-item menu-item-home">
    <a href="/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-home"></i> <br />首页</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-tags">
    <a href="/tags/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-tags"></i> <br />标签</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-categories">
    <a href="/categories/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-th"></i> <br />分类</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-archives">
    <a href="/archives/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-archive"></i> <br />归档</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-top">
    <a href="/top/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-signal"></i> <br />TopX</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-sitemap">
    <a href="/sitemap.xml" rel="section">
      <i class="menu-item-icon fa fa-fw fa-sitemap"></i> <br />站点地图</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-about">
    <a href="/about/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-user"></i> <br />关于</a>
  </li>

      
      
        <li class="menu-item menu-item-search">
          
            <a href="javascript:;" class="popup-trigger">
          
            
              <i class="menu-item-icon fa fa-search fa-fw"></i> <br />搜索</a>
        </li>
      
    </ul>
  

  
    

  

  
    <div class="site-search">
      
  <div class="popup search-popup local-search-popup">
  <div class="local-search-header clearfix">
    <span class="search-icon">
      <i class="fa fa-search"></i>
    </span>
    <span class="popup-btn-close">
      <i class="fa fa-times-circle"></i>
    </span>
    <div class="local-search-input-wrapper">
      <input autocomplete="off"
             placeholder="输入关键字搜索..." spellcheck="false"
             type="text" id="local-search-input">
    </div>
  </div>
  <div id="local-search-result"></div>
</div>



    </div>
  
</nav>



  



</div>
    </header>

    


    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="https://yfzhou.coding.me/2018/09/10/业界难题 - 『跨库分页』的四种方案/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="Felix">
      <meta itemprop="description" content="周宇峰的博客">
      <meta itemprop="image" content="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/user_head_img.jpg">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Felix">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">业界难题 - 『跨库分页』的四种方案
              
            
          </h1>
        

        <div class="post-meta">
          <span class="post-time">

            
            
            

            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              

              
                
              

              <time title="创建时间：2018-09-10 17:43:31" itemprop="dateCreated datePublished" datetime="2018-09-10T17:43:31+08:00">2018-09-10</time>
            

            
              

              
                
                <span class="post-meta-divider">|</span>
                

                <span class="post-meta-item-icon">
                  <i class="fa fa-calendar-check-o"></i>
                </span>
                
                  <span class="post-meta-item-text">更新于</span>
                
                <time title="修改时间：2019-04-17 16:59:25" itemprop="dateModified" datetime="2019-04-17T16:59:25+08:00">2019-04-17</time>
              
            
          </span>

          
            <span class="post-category" >
            
              <span class="post-meta-divider">|</span>
            
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              
                <span class="post-meta-item-text">分类于</span>
              
              
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing"><a href="/categories/数据库/" itemprop="url" rel="index"><span itemprop="name">数据库</span></a></span>

                
                
              
            </span>
          

          
            
          

          
          
             <span id="/2018/09/10/业界难题 - 『跨库分页』的四种方案/" class="leancloud_visitors" data-flag-title="业界难题 - 『跨库分页』的四种方案">
               <span class="post-meta-divider">|</span>
               <span class="post-meta-item-icon">
                 <i class="fa fa-eye"></i>
               </span>
               
                 <span class="post-meta-item-text">阅读次数：</span>
               
                 <span class="leancloud-visitors-count"></span>
             </span>
          

          

          
            <div class="post-symbolscount">
              

              
                <span class="post-meta-item-icon">
                  <i class="fa fa-edit"></i>
                </span>
                
                  <span class="post-meta-item-text">本文字数：</span>
                
                <span title="本文字数">6.3k</span>
              

              
                <span class="post-meta-divider">|</span>
              

              
                <span class="post-meta-item-icon">
                  <i class="fa fa-clock-o"></i>
                </span>
                
                  <span class="post-meta-item-text">阅读时长 &asymp;</span>
                
                <span title="阅读时长">8 分钟</span>
              
            </div>
          

          
              <div class="post-description">高并发大流量的互联网架构，一般通过服务层来访问数据库，随着数据量的增大，数据库需要进行水平切分，分库后将数据分布到不同的数据库实例（甚至物理机器）上，以达到降低数据量，增加实例数的扩容目的。</div>
          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        <p><span></span></p>
<a id="more"></a>
<h2 id="一、需求缘起"><a href="#一、需求缘起" class="headerlink" title="一、需求缘起"></a>一、需求缘起</h2><p><strong>分页需求</strong></p>
<p>互联网很多业务都有<span style="font-family: 宋体; font-size: 14px; color: rgb(255, 104, 39);">分页拉取数据</span>的需求，例如：</p>
<p>（1）微信消息过多时，拉取第N页消息</p>
<p>（2）京东下单过多时，拉取第N页订单</p>
<p>（3）浏览58同城，查看第N页帖子</p>
<p>这些业务场景对应的消息表，订单表，帖子表分页拉取需求有这样一些特点：</p>
<p>（1）<span style="font-size: 14px; color: rgb(255, 104, 39);"><span style="font-size: 14px; font-family: 宋体;">有一个业务主键</span>id</span>, 例如msg_id, order_id, tiezi_id</p>
<p>（2）分页排序是<span style="font-size: 14px; color: rgb(255, 104, 39);"><span style="font-size: 14px; font-family: 宋体;">按照非业务主键</span>id<span style="font-size: 14px; font-family: 宋体;">来排序</span></span>的，业务中经常按照时间time来排序order by</p>
<p>在数据量不大时，<strong>可以通过在排序字段time上建立索引，利用SQL提供的offset/limit功能就能满足分页查询需求</strong>：</p>
<p>select * from t_msg order by time offset 200 limit 100</p>
<p>select * from t_order order by time offset 200 limit 100</p>
<p>select * from t_tiezi order by time offset 200 limit 100</p>
<p>此处假设一页数据为100条，均拉取第3页数据。</p>
<p><strong>分库需求</strong></p>
<p>高并发大流量的互联网架构，一般通过服务层来访问数据库，<span style="font-family: 宋体; font-size: 14px; color: rgb(255, 104, 39);">随着数据量的增大，数据库需要进行水平切分</span>，分库后将数据分布到不同的数据库实例（甚至物理机器）上，以达到降低数据量，增加实例数的扩容目的。</p>
<p>一旦涉及分库，逃不开<strong>“分库依据”patition key</strong>的概念，使用哪一个字段来水平切分数据库呢：大部分的业务场景，会使用<span style="font-size: 14px; color: rgb(255, 104, 39);"><span style="font-size: 14px; font-family: 宋体;">业务主键</span>id</span>。</p>
<p>确定了分库依据patition key后，接下来要确定的是<strong>分库算法</strong>：大部分的业务场景，会使用业务<span style="font-size: 14px; color: rgb(255, 104, 39);"><span style="font-size: 14px; font-family: 宋体;">主键</span>id<span style="font-size: 14px; font-family: 宋体;">取模的算法</span></span>来分库，这样即能够保证每个库的数据分布是均匀的，又能够保证每个库的请求分布是均匀的，实在是简单实现负载均衡的好方法，此法在互联网架构中应用颇多。</p>
<p>举一个更具体的例子：</p>
<p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/1.webp" alt=""></p>
<p>用户库user，水平切分后变为两个库，分库依据patition key是uid，分库算法是uid取模：uid%2余0的数据会落到db0，uid%2余1的数据会落到db1。</p>
<p><strong>问题的提出</strong></p>
<p>仍然是上述用户库的例子，如果业务要查询“最近注册的第3页用户”，该如何实现呢？单库上，可以</p>
<p>select * from t_user order by time offset 200 limit 100</p>
<p>变成两个库后，分库依据是uid，排序依据是time，数据库层失去了time排序的全局视野，数据分布在两个库上，此时该怎么办呢？</p>
<p><span style="font-size: 14px; line-height: 1.6; font-family: 宋体; color: rgb(255, 104, 39);">如何满足“跨越多个水平切分数据库，且分库依据与排序依据为不同属性，并需要进行分页”的查询需求</span>，实现 select * from T order by time offset X limit Y的跨库分页SQL，是<span style="font-size: 14px; line-height: 1.6; font-family: 宋体; color: rgb(255, 104, 39);">本文将要讨论的技术问题</span>。</p>
<h2 id="二、全局视野法"><a href="#二、全局视野法" class="headerlink" title="二、全局视野法"></a>二、全局视野法</h2><p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/2.webp" alt=""></p>
<p>如上图所述，服务层通过uid取模将数据分布到两个库上去之后，每个数据库都失去了全局视野，数据按照time局部排序之后，不管哪个分库的第3页数据，都不一定是全局排序的第3页数据。</p>
<p>那到底哪些数据才是全局排序的第3页数据呢，暂且分三种情况讨论。</p>
<p>（1）<strong>极端情况</strong>，<span style="font-size: 14px; font-family: 宋体; color: rgb(255, 104, 39);">两个库的数据完全一样</span></p>
<p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/3.webp" alt=""></p>
<p>如果两个库的数据完全相同，只需要每个库offset一半，再取半页，就是最终想要的数据（如上图中粉色部分数据）。</p>
<p>（2）<strong>极端情况</strong>，<span style="font-size: 14px; font-family: 宋体; color: rgb(255, 104, 39);">结果数据来自一个库</span></p>
<p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/4.webp" alt=""></p>
<p>也可能两个库的数据分布及其不均衡，例如db0的所有数据的time都大于db1的所有数据的time，则可能出现：一个库的第3页数据，就是全局排序后的第3页数据（如上图中粉色部分数据）。</p>
<p>（3）<strong>一般情况</strong>，<span style="font-size: 14px; font-family: 宋体; color: rgb(255, 104, 39);">每个库数据各包含一部分</span></p>
<p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/5.webp" alt=""></p>
<p>正常情况下，全局排序的第3页数据，每个库都会包含一部分（如上图中粉色部分数据）。</p>
<p>由于不清楚到底是哪种情况，所以<span style="font-size: 14px; color: rgb(255, 104, 39);"><span style="font-size: 14px; font-family: 宋体;">必须每个库都返回</span>3<span style="font-size: 14px; font-family: 宋体;">页数据，所得到的</span>6<span style="font-size: 14px; font-family: 宋体;">页数据在服务层进行内存排序，得到数据全局视野，再取第</span>3<span style="font-size: 14px; font-family: 宋体;">页数据</span></span>，便能够得到想要的全局分页数据。</p>
<p>再总结一下这个方案的步骤：</p>
<p>（1）将order by time offset X limit Y，改写成order by time offset 0 limit X+Y</p>
<p>（2）服务层将改写后的SQL语句发往各个分库：即例子中的各取3页数据</p>
<p>（3）假设共分为N个库，服务层将得到N*(X+Y)条数据：即例子中的6页数据</p>
<p>（4）服务层对得到的N*(X+Y)条数据进行内存排序，内存排序后再取偏移量X后的Y条记录，就是全局视野所需的一页数据</p>
<p><strong>方案优点</strong>：通过服务层修改SQL语句，扩大数据召回量，能够得到全局视野，业务无损，精准返回所需数据。</p>
<p><strong>方案缺点</strong>（显而易见）：</p>
<p>（1）每个分库需要返回更多的数据，增大了网络传输量（<span style="font-size: 14px; font-family: 宋体; color: rgb(255, 104, 39);">耗网络</span>）；</p>
<p>（2）除了数据库按照time进行排序，服务层还需要进行二次排序，增大了服务层的计算量（<span style="font-size: 14px; color: rgb(255, 104, 39);"><span style="font-size: 14px; font-family: 宋体;">耗</span>CPU</span>）；</p>
<p>（3）最致命的，这个算法<span style="font-size: 14px; font-family: 宋体; color: rgb(255, 104, 39);">随着页码的增大，性能会急剧下降</span>，这是因为SQL改写后每个分库要返回X+Y行数据：返回第3页，offset中的X=200；假如要返回第100页，offset中的X=9900，即每个分库要返回100页数据，数据量和排序量都将大增，性能平方级下降。</p>
<h2 id="三、业务折衷法"><a href="#三、业务折衷法" class="headerlink" title="三、业务折衷法"></a>三、业务折衷法</h2><p>“全局视野法”虽然性能较差，但其业务无损，数据精准，不失为一种方案，有没有性能更优的方案呢？</p>
<p>“<strong>任何脱离业务的架构设计都是耍流氓</strong>”，技术方案需要折衷，在技术难度较大的情况下，<span style="font-family: 宋体; font-size: 14px; color: rgb(255, 104, 39);">业务需求的折衷能够极大的简化技术方案</span>。</p>
<p><strong>业务折衷一：禁止跳页查询</strong></p>
<p>在数据量很大，翻页数很多的时候，很多产品并<span style="font-family: 宋体; font-size: 14px; color: rgb(255, 104, 39);">不提供“直接跳到指定页面”的功能，而只提供“下一页”的功能</span>，这一个小小的业务折衷，就能极大的降低技术方案的复杂度。</p>
<p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/6.webp" alt=""></p>
<p>如上图，不够跳页，那么第一次只能够查第一页：</p>
<p>（1）将查询order by time offset 0 limit 100，改写成order by time where time&gt;0 limit 100</p>
<p>（2）上述改写和offset 0 limit 100的效果相同，都是每个分库返回了一页数据（上图中粉色部分）；</p>
<p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/7.webp" alt=""></p>
<p>（3）服务层得到2页数据，内存排序，取出前100条数据，作为最终的第一页数据，这个全局的第一页数据，一般来说每个分库都包含一部分数据（如上图粉色部分）；</p>
<p>咦，这个方案也需要服务器内存排序，岂不是和“全局视野法”一样么？<span style="font-family: 宋体; font-size: 14px; color: rgb(255, 104, 39);">第一页数据的拉取确实一样，但每一次“下一页”拉取的方案就不一样了</span>。</p>
<p>点击“下一页”时，需要拉取第二页数据，在第一页数据的基础之上，能够找到第一页数据time的最大值：</p>
<p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/8.webp" alt=""></p>
<p>这个<span style="font-size: 14px; color: rgb(255, 104, 39);"><span style="font-size: 14px; font-family: 宋体;">上一页记录的</span>time_max<span style="font-size: 14px; font-family: 宋体;">，会作为第二页数据拉取的查询条件</span></span>：</p>
<p>（1）将查询order by time offset 100 limit 100，改写成order by time where time&gt;$time_max limit 100</p>
<p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/9.webp" alt=""></p>
<p>（2）这下不是返回2页数据了（“全局视野法，会改写成offset 0 limit 200”），每个分库还是返回一页数据（如上图中粉色部分）；</p>
<p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/10.webp" alt=""></p>
<p>（3）服务层得到2页数据，内存排序，取出前100条数据，作为最终的第2页数据，这个全局的第2页数据，一般来说也是每个分库都包含一部分数据（如上图粉色部分）；</p>
<p>如此往复，查询全局视野第100页数据时，不是将查询条件改写为offset 0 limit 9900+100（<strong>返回100页数据</strong>），而是改写为time&gt;$time_max99 limit 100（<strong>仍返回一页数据</strong>），以保证<span style="font-size: 14px; font-family: 宋体; color: rgb(255, 104, 39);">数据的传输量和排序的数据量不会随着不断翻页而导致性能下降</span>。</p>
<h2 id="业务折衷二：允许数据精度损失"><a href="#业务折衷二：允许数据精度损失" class="headerlink" title="业务折衷二：允许数据精度损失"></a>业务折衷二：允许数据精度损失</h2><p>“全局视野法”能够返回业务无损的精确数据，在查询页数较大，例如第100页时，会有性能问题，此时<span style="font-size: 14px; color: rgb(255, 104, 39);"><span style="font-size: 14px; font-family: 宋体;">业务上是否能够接受，返回的</span>100<span style="font-size: 14px; font-family: 宋体;">页不是精准的数据</span></span>，而允许有一些数据偏差呢？</p>
<p><strong>数据库分库-**</strong>数据均衡原理**</p>
<p>使用patition key进行分库，在数据量较大，数据分布足够随机的情况下，<span style="font-size: 14px; color: rgb(255, 104, 39);"><span style="font-size: 14px; font-family: 宋体;">各分库所有非</span>patition key<span style="font-size: 14px; font-family: 宋体;">属性，在各个分库上的数据分布，统计概率情况是一致的</span></span>。</p>
<p>例如，在uid随机的情况下，使用uid取模分两库，db0和db1：</p>
<p>（1）<strong>性别</strong>属性，如果db0库上的男性用户占比70%，则db1上男性用户占比也应为70%</p>
<p>（2）<strong>年龄</strong>属性，如果db0库上18-28岁少女用户比例占比15%，则db1上少女用户比例也应为15%</p>
<p>（3）<strong>时间</strong>属性，如果db0库上每天10:00之前登录的用户占比为20%，则db1上应该是相同的统计规律</p>
<p>…</p>
<p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/11.webp" alt=""></p>
<p>利用这一原理，要查询全局100页数据，offset 9900 limit 100改写为offset 4950 limit 50，每个分库偏移4950（一半），获取50条数据（半页），得到的数据集的并集，基本能够认为，是全局数据的offset 9900 limit 100的数据，当然，这一页数据的精度，并不是精准的。</p>
<p>根据实际业务经验，用户都要查询第100页网页、帖子、邮件的数据了，这一页数据的精准性损失，业务上往往是可以接受的，但此时<span style="font-size: 14px; font-family: 宋体; color: rgb(255, 104, 39);">技术方案的复杂度便大大降低</span>了，既不需要返回更多的数据，也不需要进行服务内存排序了。</p>
<h2 id="四、终极武器-二次查询法"><a href="#四、终极武器-二次查询法" class="headerlink" title="四、终极武器-二次查询法"></a>四、终极武器<strong><strong>-</strong></strong>二次查询法</h2><p>有没有一种技术方案，<span style="font-family: 宋体; font-size: 14px; color: rgb(255, 104, 39);">即能够满足业务的精确需要，无需业务折衷，又高性能的方法</span>呢？这就是接下来要介绍的终极武器：“二次查询法”。</p>
<p>为了方便举例，假设一页只有5条数据，查询第200页的SQL语句为select * from T order by time offset 1000 limit 5;</p>
<p><strong>步骤一：查询改写</strong></p>
<p>将select * from T order by time offset 1000 limit 5</p>
<p>改写为select * from T order by time offset 500 limit 5</p>
<p>并投递给所有的分库，注意，这个offset的500，来自于全局offset的总偏移量1000，除以水平切分数据库个数2。</p>
<p>如果是3个分库，则可以改写为select * from T order by time offset 333 limit 5</p>
<p>假设这三个分库返回的数据(time, uid)如下：</p>
<p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/12.webp" alt=""></p>
<p>可以看到，每个分库都是返回的按照time排序的一页数据。</p>
<p><strong>步骤二：找到所返回3页全部数据的最小值</strong></p>
<p>第一个库，5条数据的time最小值是1487501123</p>
<p>第二个库，5条数据的time最小值是1487501133</p>
<p>第三个库，5条数据的time最小值是1487501143</p>
<p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/13.webp" alt=""></p>
<p>故，三页数据中，time最小值来自第一个库，time_min=1487501123，这个过程只需要比较各个分库第一条数据，时间复杂度很低</p>
<p><strong>步骤三：查询二次改写</strong></p>
<p>第一次改写的SQL语句是select * from T order by time offset 333 limit 5</p>
<p><span style="font-size: 14px; color: rgb(255, 104, 39);"><span style="font-size: 14px; font-family: 宋体;">第二次要改写成一个</span>between<span style="font-size: 14px; font-family: 宋体;">语句</span></span>，between的起点是time_min，between的终点是原来每个分库各自返回数据的最大值：</p>
<p>第一个分库，第一次返回数据的最大值是1487501523</p>
<p>所以查询改写为select * from T order by time where time between time_min and 1487501523</p>
<p>第二个分库，第一次返回数据的最大值是1487501323</p>
<p>所以查询改写为select * from T order by time where time between time_min and 1487501323</p>
<p>第三个分库，第一次返回数据的最大值是1487501553</p>
<p>所以查询改写为select * from T order by time where time between time_min and 1487501553</p>
<p><span style="font-family: 宋体; font-size: 14px; color: rgb(255, 104, 39);">相对第一次查询，第二次查询条件放宽了，故第二次查询会返回比第一次查询结果集更多的数据</span>，假设这三个分库返回的数据(time, uid)如下：</p>
<p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/14.webp" alt=""></p>
<p>可以看到：</p>
<p>由于time_min来自原来的分库一，所以分库一的返回结果集和第一次查询相同（所以其实这次访问是可以省略的）；</p>
<p>分库二的结果集，比第一次多返回了1条数据，头部的1条记录（time最小的记录）是新的（上图中粉色记录）；</p>
<p>分库三的结果集，比第一次多返回了2条数据，头部的2条记录（time最小的2条记录）是新的（上图中粉色记录）；</p>
<p><strong>步骤四：在每个结果集中虚拟一个time_min记录，找到time_min在全局的offset</strong></p>
<p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/15.webp" alt=""></p>
<p>在第一个库中，time_min在第一个库的offset是333</p>
<p>在第二个库中，(1487501133, uid_aa)的offset是333（根据第一次查询条件得出的），故虚拟time_min在第二个库的offset是331</p>
<p>在第三个库中，(1487501143, uid_aaa)的offset是333（根据第一次查询条件得出的），故虚拟time_min在第三个库的offset是330</p>
<p>综上，time_min在全局的offset是333+331+330=994</p>
<p><strong>步骤五：既然得到了time_min在全局的offset，就相当于有了全局视野，根据第二次的结果集，就能够得到全局offset 1000 limit 5的记录</strong></p>
<p><img src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/16.webp" alt=""></p>
<p>第二次查询在各个分库返回的结果集是有序的，又知道了time_min在全局的offset是994，一路排下来，容易知道全局offset 1000 limit 5的一页记录（上图中黄色记录）。</p>
<p>是不是非常巧妙？这种方法的<strong>优点</strong>是：可以<span style="font-family: 宋体; font-size: 14px; color: rgb(255, 104, 39);">精确的返回业务所需数据，每次返回的数据量都非常小，不会随着翻页增加数据的返回量</span>。</p>
<p><strong>不足是</strong>：需要进行<span style="font-family: 宋体; font-size: 14px; color: rgb(255, 104, 39);">两次数据库查询</span>。</p>
<p><strong>五、总结</strong></p>
<p>今天介绍了解决“跨N库分页”这一难题的四种方法：</p>
<p><strong>方法一：全局视野法</strong></p>
<p>（1）将order by time offset X limit Y，改写成order by time offset 0 limit X+Y</p>
<p>（2）服务层对得到的N*(X+Y)条数据进行内存排序，内存排序后再取偏移量X后的Y条记录</p>
<p>这种方法<span style="font-family: 宋体; font-size: 14px; color: rgb(255, 104, 39);">随着翻页的进行，性能越来越低</span>。</p>
<p><strong>方法二：业务折衷法-禁止跳页查询</strong></p>
<p>（1）用正常的方法取得第一页数据，并得到第一页记录的time_max</p>
<p>（2）每次翻页，将order by time offset X limit Y，改写成order by time where time&gt;$time_max limit Y</p>
<p>以保证<span style="font-family: 宋体; font-size: 14px; color: rgb(255, 104, 39);">每次只返回一页数据，性能为常量</span>。</p>
<p><strong>方法三：业务折衷法-允许模糊数据</strong></p>
<p>（1）将order by time offset X limit Y，改写成order by time offset X/N limit Y/N</p>
<p><strong>方法四：二次查询法</strong></p>
<p>（1）将order by time offset X limit Y，改写成order by time offset X/N limit Y</p>
<p>（2）找到最小值time_min</p>
<p>（3）between二次查询，order by time between $time_min and $time_i_max</p>
<p>（4）设置虚拟time_min，找到time_min在各个分库的offset，从而得到time_min在全局的offset</p>
<p>（5）得到了time_min在全局的offset，自然得到了全局的offset X limit Y</p>
<p>原创： 58沈剑</p>
<p>转载来源：<a href="https://mp.weixin.qq.com/s/B9FHHM-NCZvtn7xnawYl1w" target="_blank" rel="noopener">业界难题-“跨库分页”的四种方案</a></p>

      
    </div>

    

    
    
    

    

    
      <div>
        <div style="padding: 10px 0; margin: 20px auto; width: 90%; text-align: center;">
  <div>如果你觉得这篇文章对你有用，欢迎赞赏哦~</div>
  <button id="rewardButton" disable="enable" onclick="var qr = document.getElementById('QR'); if (qr.style.display === 'none') {qr.style.display='block';} else {qr.style.display='none'}">
    <span>打赏</span>
  </button>
  <div id="QR" style="display: none;">

    
      <div id="wechat" style="display: inline-block">
        <img id="wechat_qr" src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/wechatpay.png" alt="Felix 微信支付"/>
        <p>微信支付</p>
      </div>
    

    
      <div id="alipay" style="display: inline-block">
        <img id="alipay_qr" src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/alipay.png" alt="Felix 支付宝"/>
        <p>支付宝</p>
      </div>
    

    

  </div>
</div>

      </div>
    

    
	
	<div>
  
    <div>
    
        <div style="text-align: center;font-size: 13px;letter-spacing: 10px;user-select: none;margin-top: 50px;color: #bbb;">本文结束啦 <i class="fa fa-star"></i> 感谢您阅读</div>
    
</div>
  
</div>

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/tags/数据库/" rel="tag"><i class="fa fa-tag"></i> 数据库</a>
          
        </div>
      

      
      
        <div class="post-widgets">
        

        

        
          
          <div class="social_share">
            
            
               <div id="needsharebutton-postbottom">
                 <span class="btn" title="分享">
                    <i class="fa fa-share-alt" aria-hidden="true"></i>
                 </span>
               </div>
            
          </div>
        
        </div>
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
			
			   <a href="/2018/09/17/Hexo-Next搭建个人博客（Hexo博客备份）/" rel="prev" title="Hexo-Next搭建个人博客（Hexo博客备份）">
			   <i class="fa fa-chevron-left"></i> Hexo-Next搭建个人博客（Hexo博客备份）
			   
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
             
				<a href="/2018/09/06/Python优雅写法，让你工作效率翻2倍/" rel="next" title="Python优雅写法，让你工作效率翻2倍">
				Python优雅写法，让你工作效率翻2倍 <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </div>
  
  
  
  </article>


  </div>


          </div>
          

  
    <div class="comments" id="comments">
      <div id="lv-container" data-id="city" data-uid="MTAyMC8zODUyNC8xNTA1Mg=="></div>
    </div>

  



        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    
      <div id="sidebar-dimmer"></div>
    
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview-wrap">
            站点概览
          </li>
        </ul>
      

      <section class="site-overview-wrap sidebar-panel">
        <div class="site-overview">
          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
            
			<a href="/">
              <img class="site-author-image" itemprop="image"
                src="https://yfzhou.oss-cn-beijing.aliyuncs.com/blog/img/user_head_img.jpg"
                alt="Felix" />
				</a>
            
              <p class="site-author-name" itemprop="name">Felix</p>
              <p class="site-description motion-element" itemprop="description">周宇峰的博客</p>
          </div>

          
            <nav class="site-state motion-element">
              
                <div class="site-state-item site-state-posts">
                
                  <a href="/archives/">
                
                    <span class="site-state-item-count">94</span>
                    <span class="site-state-item-name">日志</span>
                  </a>
                </div>
              

              
                
                
                <div class="site-state-item site-state-categories">
                  <a href="/categories/index.html">
                    
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                    <span class="site-state-item-count">16</span>
                    <span class="site-state-item-name">分类</span>
                  </a>
                </div>
              

              
                
                
                <div class="site-state-item site-state-tags">
                  <a href="/tags/index.html">
                    
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                    <span class="site-state-item-count">88</span>
                    <span class="site-state-item-name">标签</span>
                  </a>
                </div>
              
            </nav>
          

          
            <div class="feed-link motion-element">
              <a href="/atom.xml" rel="alternate">
                <i class="fa fa-rss"></i>
                RSS
              </a>
            </div>
          

          
            <div class="links-of-author motion-element">
              
                <span class="links-of-author-item">
                  <a href="https://github.com/542869246" target="_blank" title="GitHub"><i class="fa fa-fw fa-github"></i>GitHub</a>
                  
                </span>
              
                <span class="links-of-author-item">
                  <a href="https://gitee.com/zyf542869246" target="_blank" title="Gitee"><i class="fa fa-fw fa-git"></i>Gitee</a>
                  
                </span>
              
                <span class="links-of-author-item">
                  <a href="https://music.163.com/#/user/home?id=343583524" target="_blank" title="网易云音乐"><i class="fa fa-fw fa-music"></i>网易云音乐</a>
                  
                </span>
              
                <span class="links-of-author-item">
                  <a href="https://www.zhihu.com/people/yu-sui-58/activities" target="_blank" title="知乎"><i class="fa fa-fw fa-globe"></i>知乎</a>
                  
                </span>
              
            </div>
          

          
          

          
          
            <div class="links-of-blogroll motion-element links-of-blogroll-block">
              <div class="links-of-blogroll-title">
                <i class="fa  fa-fw fa-link"></i>
                友情链接
              </div>
              <ul class="links-of-blogroll-list">
                
                  <li class="links-of-blogroll-item">
                    <a href="http://zblog.dawnings.top/" title="删库东😎" target="_blank">删库东😎</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="https://ntssl.cn" title="ntssl.cn" target="_blank">ntssl.cn</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="http://blog.imyzt.top/" title="imyzt" target="_blank">imyzt</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="http://www.charmsongo.cn/" title="charmsongo" target="_blank">charmsongo</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="https://www.52share.online" title="HuiProgramer" target="_blank">HuiProgramer</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="https://music.163.com/#/user/home?id=343583524" title="😆 网易云音乐 😆" target="_blank">😆 网易云音乐 😆</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="https://www.zhihu.com/people/yu-sui-58/activities" title="😤 知乎 😤" target="_blank">😤 知乎 😤</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="http://www.ityouknow.com/spring-boot.html" title="spring-boot 纯洁的微笑" target="_blank">spring-boot 纯洁的微笑</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="https://www.liaoxuefeng.com/wiki/0014316089557264a6b348958f449949df42a6d3a2e542c000" title="Python教程 廖雪峰" target="_blank">Python教程 廖雪峰</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="/about" title="关于此博客" target="_blank">关于此博客</a>
                  </li>
                
              </ul>
			  <div id="days"></div>
<script>
function show_date_time(){
window.setTimeout("show_date_time()", 1000);
BirthDay=new Date("07/30/2018 15:13:14");
today=new Date();
timeold=(today.getTime()-BirthDay.getTime());
sectimeold=timeold/1000
secondsold=Math.floor(sectimeold);
msPerDay=24*60*60*1000
e_daysold=timeold/msPerDay
daysold=Math.floor(e_daysold);
e_hrsold=(e_daysold-daysold)*24;
hrsold=setzero(Math.floor(e_hrsold));
e_minsold=(e_hrsold-hrsold)*60;
minsold=setzero(Math.floor((e_hrsold-hrsold)*60));
seconds=setzero(Math.floor((e_minsold-minsold)*60));
document.getElementById('days').innerHTML="已运行"+daysold+"天"+hrsold+"小时"+minsold+"分"+seconds+"秒";
}
function setzero(i){
if (i<10)
{i="0" + i};
return i;
}
show_date_time();
</script>
            </div>
          

          
            
          
          <div id="days"></div>
<script>
function show_date_time(){
window.setTimeout("show_date_time()", 1000);
BirthDay=new Date("07/30/2018 15:13:14");
today=new Date();
timeold=(today.getTime()-BirthDay.getTime());
sectimeold=timeold/1000
secondsold=Math.floor(sectimeold);
msPerDay=24*60*60*1000
e_daysold=timeold/msPerDay
daysold=Math.floor(e_daysold);
e_hrsold=(e_daysold-daysold)*24;
hrsold=setzero(Math.floor(e_hrsold));
e_minsold=(e_hrsold-hrsold)*60;
minsold=setzero(Math.floor((e_hrsold-hrsold)*60));
seconds=setzero(Math.floor((e_minsold-minsold)*60));
document.getElementById('days').innerHTML="已运行"+daysold+"天"+hrsold+"小时"+minsold+"分"+seconds+"秒";
}
function setzero(i){
if (i<10)
{i="0" + i};
return i;
}
show_date_time();
</script>
        </div>
      </section>

      
      <!--noindex-->
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
              
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#一、需求缘起"><span class="nav-number">1.</span> <span class="nav-text">一、需求缘起</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#二、全局视野法"><span class="nav-number">2.</span> <span class="nav-text">二、全局视野法</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#三、业务折衷法"><span class="nav-number">3.</span> <span class="nav-text">三、业务折衷法</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#业务折衷二：允许数据精度损失"><span class="nav-number">4.</span> <span class="nav-text">业务折衷二：允许数据精度损失</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#四、终极武器-二次查询法"><span class="nav-number">5.</span> <span class="nav-text">四、终极武器-二次查询法</span></a></li></ol></div>
            

          </div>
        </section>
      <!--/noindex-->
      

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright">&copy; 2018 – <span itemprop="copyrightYear">2019</span>
  <span class="with-love" id="animate">
    <i class="fa fa-tint"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">Felix</span>

  
    <span class="post-meta-divider">|</span>
    <span class="post-meta-item-icon">
      <i class="fa fa-area-chart"></i>
    </span>
    
      <span class="post-meta-item-text">站点总字数：</span>
    
    <span title="站点总字数">538k</span>
  

  
    <span class="post-meta-divider">|</span>
    <span class="post-meta-item-icon">
      <i class="fa fa-coffee"></i>
    </span>
    
      <span class="post-meta-item-text">站点阅读时长 &asymp;</span>
    
    <span title="站点阅读时长">11:13</span>
  
  
  <div style="background-image:linear-gradient(90deg,#6dba82 0,#07b39b 15%,#1098ad 30%,#5073b8 44%,#a166ab 58%,#ef4e7b 72%,#f37055 86%,#f79533 100%);background-size:cover;-webkit-background-clip:text;-webkit-text-fill-color:transparent;user-select:none"><i>Tip: 本站电脑访问体验更佳耶&nbsp;<i class="fa fa-angellist" style="display:inline"></i></i></div>
  
</div>


<!--

  <div class="powered-by">由 <a class="theme-link" target="_blank" href="https://hexo.io">Hexo</a> 强力驱动 v3.7.1</div>





-->



  <span>Hosted by <a href="https://pages.coding.me" >Coding Pages</a></span>



<div class="weixin-box">
  <div class="weixin-menu">
    <div class="weixin-hover">
      <div class="weixin-description">微信扫一扫，加好友</div>
    </div>
  </div>
</div>

        
<div class="busuanzi-count">
  <script async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>

  
    <span class="site-uv" title="总访客量">
      <i class="fa fa-user"></i>
      <span class="busuanzi-value" id="busuanzi_value_site_uv"></span>
    </span>
  

  
    <span class="site-pv" title="总访问量">
      <i class="fa fa-eye"></i>
      <span class="busuanzi-value" id="busuanzi_value_site_pv"></span>
    </span>
  
</div>









        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
          <span id="scrollpercent"><span>0</span>%</span>
        
      </div>
    

    
      <div id="needsharebutton-float">
        <span class="btn">
          <i class="fa fa-share-alt" aria-hidden="true"></i>
        </span>
      </div>
    
	
    

    
  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>












  















  
  
    <script type="text/javascript" src="//cdn.jsdelivr.net/jquery/2.1.3/jquery.min.js"></script>
  

  
  
    <script type="text/javascript" src="//cdn.jsdelivr.net/velocity/1.2.3/velocity.min.js"></script>
  

  
  
    <script type="text/javascript" src="//cdn.jsdelivr.net/velocity/1.2.3/velocity.ui.min.js"></script>
  

  
  
    <script type="text/javascript" src="//cdn.jsdelivr.net/fancybox/2.1.5/jquery.fancybox.pack.js"></script>
  


  


  <script type="text/javascript" src="/js/src/utils.js?v=6.4.1"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=6.4.1"></script>



  
  

  
  <script type="text/javascript" src="/js/src/scrollspy.js?v=6.4.1"></script>
<script type="text/javascript" src="/js/src/post-details.js?v=6.4.1"></script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=6.4.1"></script>



  



  
    <script type="text/javascript">
      window.livereOptions = {
        refer: '2018/09/10/业界难题 - 『跨库分页』的四种方案/'
      };
      (function(d, s) {
        var j, e = d.getElementsByTagName(s)[0];
        if (typeof LivereTower === 'function') { return; }
        j = d.createElement(s);
        j.src = 'https://cdn-city.livere.com/js/embed.dist.js';
        j.async = true;
        e.parentNode.insertBefore(j, e);
      })(document, 'script');
    </script>
  









  

  <script type="text/javascript">
    // Popup Window;
    var isfetched = false;
    var isXml = true;
    // Search DB path;
    var search_path = "search.xml";
    if (search_path.length === 0) {
      search_path = "search.xml";
    } else if (/json$/i.test(search_path)) {
      isXml = false;
    }
    var path = "/" + search_path;
    // monitor main search box;

    var onPopupClose = function (e) {
      $('.popup').hide();
      $('#local-search-input').val('');
      $('.search-result-list').remove();
      $('#no-result').remove();
      $(".local-search-pop-overlay").remove();
      $('body').css('overflow', '');
    }

    function proceedsearch() {
      $("body")
        .append('<div class="search-popup-overlay local-search-pop-overlay"></div>')
        .css('overflow', 'hidden');
      $('.search-popup-overlay').click(onPopupClose);
      $('.popup').toggle();
      var $localSearchInput = $('#local-search-input');
      $localSearchInput.attr("autocapitalize", "none");
      $localSearchInput.attr("autocorrect", "off");
      $localSearchInput.focus();
    }

    // search function;
    var searchFunc = function(path, search_id, content_id) {
      'use strict';

      // start loading animation
      $("body")
        .append('<div class="search-popup-overlay local-search-pop-overlay">' +
          '<div id="search-loading-icon">' +
          '<i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>' +
          '</div>' +
          '</div>')
        .css('overflow', 'hidden');
      $("#search-loading-icon").css('margin', '20% auto 0 auto').css('text-align', 'center');

      

      $.ajax({
        url: path,
        dataType: isXml ? "xml" : "json",
        async: true,
        success: function(res) {
          // get the contents from search data
          isfetched = true;
          $('.popup').detach().appendTo('.header-inner');
          var datas = isXml ? $("entry", res).map(function() {
            return {
              title: $("title", this).text(),
              content: $("content",this).text(),
              url: $("url" , this).text()
            };
          }).get() : res;
          var input = document.getElementById(search_id);
          var resultContent = document.getElementById(content_id);
          var inputEventFunction = function() {
            var searchText = input.value.trim().toLowerCase();
            var keywords = searchText.split(/[\s\-]+/);
            if (keywords.length > 1) {
              keywords.push(searchText);
            }
            var resultItems = [];
            if (searchText.length > 0) {
              // perform local searching
              datas.forEach(function(data) {
                var isMatch = false;
                var hitCount = 0;
                var searchTextCount = 0;
                var title = data.title.trim();
                var titleInLowerCase = title.toLowerCase();
                var content = data.content.trim().replace(/<[^>]+>/g,"");
                
                var contentInLowerCase = content.toLowerCase();
                var articleUrl = decodeURIComponent(data.url);
                var indexOfTitle = [];
                var indexOfContent = [];
                // only match articles with not empty titles
                if(title != '') {
                  keywords.forEach(function(keyword) {
                    function getIndexByWord(word, text, caseSensitive) {
                      var wordLen = word.length;
                      if (wordLen === 0) {
                        return [];
                      }
                      var startPosition = 0, position = [], index = [];
                      if (!caseSensitive) {
                        text = text.toLowerCase();
                        word = word.toLowerCase();
                      }
                      while ((position = text.indexOf(word, startPosition)) > -1) {
                        index.push({position: position, word: word});
                        startPosition = position + wordLen;
                      }
                      return index;
                    }

                    indexOfTitle = indexOfTitle.concat(getIndexByWord(keyword, titleInLowerCase, false));
                    indexOfContent = indexOfContent.concat(getIndexByWord(keyword, contentInLowerCase, false));
                  });
                  if (indexOfTitle.length > 0 || indexOfContent.length > 0) {
                    isMatch = true;
                    hitCount = indexOfTitle.length + indexOfContent.length;
                  }
                }

                // show search results

                if (isMatch) {
                  // sort index by position of keyword

                  [indexOfTitle, indexOfContent].forEach(function (index) {
                    index.sort(function (itemLeft, itemRight) {
                      if (itemRight.position !== itemLeft.position) {
                        return itemRight.position - itemLeft.position;
                      } else {
                        return itemLeft.word.length - itemRight.word.length;
                      }
                    });
                  });

                  // merge hits into slices

                  function mergeIntoSlice(text, start, end, index) {
                    var item = index[index.length - 1];
                    var position = item.position;
                    var word = item.word;
                    var hits = [];
                    var searchTextCountInSlice = 0;
                    while (position + word.length <= end && index.length != 0) {
                      if (word === searchText) {
                        searchTextCountInSlice++;
                      }
                      hits.push({position: position, length: word.length});
                      var wordEnd = position + word.length;

                      // move to next position of hit

                      index.pop();
                      while (index.length != 0) {
                        item = index[index.length - 1];
                        position = item.position;
                        word = item.word;
                        if (wordEnd > position) {
                          index.pop();
                        } else {
                          break;
                        }
                      }
                    }
                    searchTextCount += searchTextCountInSlice;
                    return {
                      hits: hits,
                      start: start,
                      end: end,
                      searchTextCount: searchTextCountInSlice
                    };
                  }

                  var slicesOfTitle = [];
                  if (indexOfTitle.length != 0) {
                    slicesOfTitle.push(mergeIntoSlice(title, 0, title.length, indexOfTitle));
                  }

                  var slicesOfContent = [];
                  while (indexOfContent.length != 0) {
                    var item = indexOfContent[indexOfContent.length - 1];
                    var position = item.position;
                    var word = item.word;
                    // cut out 100 characters
                    var start = position - 20;
                    var end = position + 80;
                    if(start < 0){
                      start = 0;
                    }
                    if (end < position + word.length) {
                      end = position + word.length;
                    }
                    if(end > content.length){
                      end = content.length;
                    }
                    slicesOfContent.push(mergeIntoSlice(content, start, end, indexOfContent));
                  }

                  // sort slices in content by search text's count and hits' count

                  slicesOfContent.sort(function (sliceLeft, sliceRight) {
                    if (sliceLeft.searchTextCount !== sliceRight.searchTextCount) {
                      return sliceRight.searchTextCount - sliceLeft.searchTextCount;
                    } else if (sliceLeft.hits.length !== sliceRight.hits.length) {
                      return sliceRight.hits.length - sliceLeft.hits.length;
                    } else {
                      return sliceLeft.start - sliceRight.start;
                    }
                  });

                  // select top N slices in content

                  var upperBound = parseInt('1');
                  if (upperBound >= 0) {
                    slicesOfContent = slicesOfContent.slice(0, upperBound);
                  }

                  // highlight title and content

                  function highlightKeyword(text, slice) {
                    var result = '';
                    var prevEnd = slice.start;
                    slice.hits.forEach(function (hit) {
                      result += text.substring(prevEnd, hit.position);
                      var end = hit.position + hit.length;
                      result += '<b class="search-keyword">' + text.substring(hit.position, end) + '</b>';
                      prevEnd = end;
                    });
                    result += text.substring(prevEnd, slice.end);
                    return result;
                  }

                  var resultItem = '';

                  if (slicesOfTitle.length != 0) {
                    resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + highlightKeyword(title, slicesOfTitle[0]) + "</a>";
                  } else {
                    resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + title + "</a>";
                  }

                  slicesOfContent.forEach(function (slice) {
                    resultItem += "<a href='" + articleUrl + "'>" +
                      "<p class=\"search-result\">" + highlightKeyword(content, slice) +
                      "...</p>" + "</a>";
                  });

                  resultItem += "</li>";
                  resultItems.push({
                    item: resultItem,
                    searchTextCount: searchTextCount,
                    hitCount: hitCount,
                    id: resultItems.length
                  });
                }
              })
            };
            if (keywords.length === 1 && keywords[0] === "") {
              resultContent.innerHTML = '<div id="no-result"><i class="fa fa-search fa-5x" /></div>'
            } else if (resultItems.length === 0) {
              resultContent.innerHTML = '<div id="no-result"><i class="fa fa-frown-o fa-5x" /></div>'
            } else {
              resultItems.sort(function (resultLeft, resultRight) {
                if (resultLeft.searchTextCount !== resultRight.searchTextCount) {
                  return resultRight.searchTextCount - resultLeft.searchTextCount;
                } else if (resultLeft.hitCount !== resultRight.hitCount) {
                  return resultRight.hitCount - resultLeft.hitCount;
                } else {
                  return resultRight.id - resultLeft.id;
                }
              });
              var searchResultList = '<ul class=\"search-result-list\">';
              resultItems.forEach(function (result) {
                searchResultList += result.item;
              })
              searchResultList += "</ul>";
              resultContent.innerHTML = searchResultList;
            }
          }

          if ('auto' === 'auto') {
            input.addEventListener('input', inputEventFunction);
          } else {
            $('.search-icon').click(inputEventFunction);
            input.addEventListener('keypress', function (event) {
              if (event.keyCode === 13) {
                inputEventFunction();
              }
            });
          }

          // remove loading animation
          $(".local-search-pop-overlay").remove();
          $('body').css('overflow', '');

          proceedsearch();
        }
      });
    }

    // handle and trigger popup window;
    $('.popup-trigger').click(function(e) {
      e.stopPropagation();
      if (isfetched === false) {
        searchFunc(path, 'local-search-input', 'local-search-result');
      } else {
        proceedsearch();
      };
    });

    $('.popup-btn-close').click(onPopupClose);
    $('.popup').click(function(e){
      e.stopPropagation();
    });
    $(document).on('keyup', function (event) {
      var shouldDismissSearchPopup = event.which === 27 &&
        $('.search-popup').is(':visible');
      if (shouldDismissSearchPopup) {
        onPopupClose();
      }
    });
  </script>





  

  
  <script>
    
    function addCount(Counter) {
      var $visitors = $(".leancloud_visitors");
      var url = $visitors.attr('id').trim();
      var title = $visitors.attr('data-flag-title').trim();

      Counter('get', '/classes/Counter', { where: JSON.stringify({ url }) })
        .done(function ({ results }) {
          if (results.length > 0) {
            var counter = results[0];
            
              var $element = $(document.getElementById(url));
              $element.find('.leancloud-visitors-count').text(counter.time + 1);
            
            Counter('put', `/classes/Counter/${counter.objectId}`, JSON.stringify({ time: { "__op":"Increment", "amount":1 } }))
            
            .fail(function ({ responseJSON }) {
                console.log('Failed to save Visitor num, with error message: ' + responseJSON.error);
            })
          } else {
            
              var $element = $(document.getElementById(url));
              $element.find('.leancloud-visitors-count').text('Counter not initialized! See more at console err msg.');
              console.error('ATTENTION! LeanCloud counter has security bug, see here how to solve it: https://github.com/theme-next/hexo-leancloud-counter-security. \n But you also can use LeanCloud without security, by set \'security\' option to \'false\'.');
            
          }
        })
      .fail(function ({ responseJSON }) {
        console.log('LeanCloud Counter Error:' + responseJSON.code + " " + responseJSON.error);
      });
    }
    

    $(function() {
      $.get('https://app-router.leancloud.cn/2/route?appId=' + "4DkhRkq5CadWFisToA3zFrop-gzGzoHsz")
        .done(function ({ api_server }) {
          var Counter = function (method, url, data) {
            return $.ajax({
              method: method,
              url: `https://${api_server}/1.1${url}`,
              headers: {
                'X-LC-Id': "4DkhRkq5CadWFisToA3zFrop-gzGzoHsz",
                'X-LC-Key': "xihAA2ETGTdekJFloguCmmgq",
                'Content-Type': 'application/json',
              },
              data: data,
            });
          };
          
          addCount(Counter);
          
        })
    });
  </script>



  

  

  

  
  
  
  <script src="/lib/needsharebutton/needsharebutton.js"></script>

  <script>
    
      pbOptions = {};
      
          pbOptions.iconStyle = "default";
      
          pbOptions.boxForm = "horizontal";
      
          pbOptions.position = "bottomCenter";
      
          pbOptions.networks = "Weibo,Wechat,Douban,QQZone,Twitter,Linkedin,Mailto,Reddit,Delicious,StumbleUpon,Pinterest,Facebook,GooglePlus,Slashdot,Technorati,Posterous,Tumblr,GoogleBookmarks,Newsvine,Evernote,Friendfeed,Vkontakte,Odnoklassniki,Mailru";
      
      new needShareButton('#needsharebutton-postbottom', pbOptions);
    
    
      flOptions = {};
      
          flOptions.iconStyle = "default";
      
          flOptions.boxForm = "horizontal";
      
          flOptions.position = "middleRight";
      
          flOptions.networks = "Weibo,Wechat,Douban,QQZone,Twitter,Linkedin,Mailto,Facebook,Tumblr,Friendfeed";
      
      new needShareButton('#needsharebutton-float', flOptions);
    
  </script>

  

  

  

  

  

  

  
  <div class="bg_content">
	<canvas id="canvas"></canvas>
	<canvas id="canvas1"></canvas>
	  <!-- html5-canvas-animation
	  <div class="canvas-wrap">
		<div id="canvas3" class="gradient"></div>
	</div>-->
  </div>
  
  
  
  <!-- 右上角github关注-->
  <div class="forkme">
	<!--<a href="https://github.com/542869246">
	<img style="position: absolute; top: 0; right: 0; border: 0;" src="http://pd2tflnys.bkt.clouddn.com/img/blog/forkme_right_red_aa0000.png" alt="Fork me on GitHub">
	</a>-->
	<a href="https://github.com/542869246" class="github-corner" aria-label="View source on GitHub">
	<svg width="80" height="80" viewBox="0 0 250 250" style="fill:#70B7FD; color:#fff; position: absolute; top: 0; border: 0; right: 0;" aria-hidden="true">
	<path d="M0,0 L115,115 L130,115 L142,142 L250,250 L250,0 Z"></path>
	<path d="M128.3,109.0 C113.8,99.7 119.0,89.6 119.0,89.6 C122.0,82.7 120.5,78.6 120.5,78.6 C119.2,72.0 123.4,76.3 123.4,76.3 C127.3,80.9 125.5,87.3 125.5,87.3 C122.9,97.6 130.6,101.9 134.4,103.2" fill="currentColor" style="transform-origin: 130px 106px;" class="octo-arm"></path><path d="M115.0,115.0 C114.9,115.1 118.7,116.5 119.8,115.4 L133.7,101.6 C136.9,99.2 139.9,98.4 142.2,98.6 C133.8,88.0 127.5,74.4 143.8,58.0 C148.5,53.4 154.0,51.2 159.7,51.0 C160.3,49.4 163.2,43.6 171.4,40.1 C171.4,40.1 176.1,42.5 178.8,56.2 C183.1,58.6 187.2,61.8 190.9,65.4 C194.5,69.0 197.7,73.2 200.1,77.6 C213.8,80.2 216.3,84.9 216.3,84.9 C212.7,93.1 206.9,96.0 205.4,96.6 C205.1,102.4 203.0,107.8 198.3,112.5 C181.9,128.9 168.3,122.5 157.7,114.1 C157.9,116.9 156.7,120.9 152.7,124.9 L141.0,136.5 C139.8,137.7 141.6,141.9 141.8,141.8 Z" fill="currentColor" class="octo-body"></path>
	</svg>
	</a>
	<style>.github-corner:hover .octo-arm{animation:octocat-wave 560ms ease-in-out}@keyframes octocat-wave{0%,100%{transform:rotate(0)}20%,60%{transform:rotate(-25deg)}40%,80%{transform:rotate(10deg)}}@media (max-width:500px){.github-corner:hover .octo-arm{animation:none}.github-corner .octo-arm{animation:octocat-wave 560ms ease-in-out}}</style>
  </div>
  
  <!-- 音乐播放器-->
  <link rel="stylesheet" href="/dist/APlayer.min.css">
  <div id="aplayer"></div>
  <script type="text/javascript" src="/dist/APlayer.min.js"></script>
  <script type="text/javascript" src="/dist/music.js"></script>
 
   <!-- 页面点击小红心 -->
<script type="text/javascript" src="/js/src/clicklove.js"></script>
 <!-- 页面点击汉字 -->
<script type="text/javascript" src="/js/src/hanzi.js"></script>
  
  <!-- 代码块复制功能 -->
<script type="text/javascript" src="/js/src/clipboard.min.js"></script>  
<script type="text/javascript" src="/js/src/clipboard-use.js"></script>
<!-- 流星雨-->
<!--<script type="text/javascript" src="/js/src/meteor-rain.js"></script>-->
<!-- 粒子效果-->
<script type="text/javascript" src="/js/src/dynamic_bg.js"></script>

<!-- html5-canvas-animation-->
<!-- Main library
<script src="/js/src/three.min.js"></script> -->
<!-- Helpers
<script src="/js/src/projector.js"></script>
<script src="/js/src/canvas-renderer.js"></script> -->
<!-- Visualitzation adjustments 
<script src="/js/src/3d-lines-animation.js"></script>-->
<!-- Animated background color 
<script src="/js/src/color.js"></script>-->



</body>
</html>
